/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package PurchaseOrder;

import StoreType.StoreTypeBean;
import User.UserBean;
import Vendor.VendorBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author aamod
 */
public class Query {

    static Connection con = null;

    public static int add(PurchaseOrderBean pb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into purchase_order values(PURCHASEORDERID.nextval,?,?,?,?,?,?,?,?,?,?,sysdate,PURCHASEORDERID.currval||'-'||sysdate,?,?,sysdate,?,0)");
            pre.setInt(1, pb.getVendorId());
            pre.setInt(2, pb.getStoreTypeId());
            pre.setString(3, pb.getProductName());
            pre.setInt(4, pb.getQty());
            pre.setInt(5, pb.getUnitPrice());
            pre.setInt(6, pb.getTax());
            pre.setInt(7, pb.gettAmount());
            pre.setInt(8, pb.getApprovalStatus());
            pre.setInt(9, pb.getApprovedBy());
            pre.setInt(10, pb.getUserId());
            pre.setDate(11, pb.getApprovedDate());
            pre.setInt(12, pb.getApprovalNo());
            pre.setString(13, pb.getDescription());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int edit(PurchaseOrderBean pb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update purchase_order set vendor_id=?, store_type_id=?,product_name=?,qty=?,unit_price=?,tax=?,total_amount=?,approval_status=?,approved_by=?,user_id=?,pod=?,pod_number=?,approval_date=?,approval_number=?,ENTRYDATE=sysdate,description=?");
            pre.setInt(1, pb.getVendorId());
            pre.setInt(2, pb.getStoreTypeId());
            pre.setString(3, pb.getProductName());
            pre.setInt(4, pb.getQty());
            pre.setInt(5, pb.getUnitPrice());
            pre.setInt(6, pb.getTax());
            pre.setInt(7, pb.gettAmount());
            pre.setInt(8, pb.getApprovalStatus());
            pre.setInt(9, pb.getApprovedBy());
            pre.setInt(10, pb.getUserId());
            pre.setDate(11, pb.getPurchaseOrderDate());
            pre.setString(12, pb.getPurchaseOrderNo());
            pre.setDate(13, pb.getApprovedDate());
            pre.setInt(14, pb.getApprovalNo());
            pre.setString(15, pb.getDescription());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(PURCHASE_ORDER_ID) from PURCHASE_ORDER");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from purchase_order left join vendor on vendor.vendor_id=purchase_order.vendor_id left join store_type s on s.store_type_id=purchase_order.store_type_id left join users on users.user_id=purchase_order.user_id where RECEIVE_STATUS!=1");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                PurchaseOrderBean pob = new PurchaseOrderBean();
                VendorBean vb = new VendorBean();
                StoreTypeBean sb = new StoreTypeBean();
                UserBean ub = new UserBean();

                pob.setOrderId(rs.getInt(1));
                pob.setVendorId(rs.getInt(2));
                pob.setStoreTypeId(rs.getInt(3));
                pob.setProductName(rs.getString(4));
                pob.setQty(rs.getInt(5));
                pob.setUnitPrice(rs.getInt(6));
                pob.setTax(rs.getInt(7));
                pob.settAmount(rs.getInt(8));
                pob.setApprovalStatus(rs.getInt(9));
                pob.setApprovedBy(rs.getInt(10));
                pob.setUserId(rs.getInt(11));
                pob.setPurchaseOrderDate(rs.getDate(12));
                pob.setPurchaseOrderNo(rs.getString(13));
                pob.setApprovedDate(rs.getDate(14));
                pob.setApprovalNo(rs.getInt(15));
                pob.setEntryDate(rs.getDate(16));
                pob.setDescription(rs.getString(17));
                pob.setReceiveStatus(rs.getInt(18));

                vb.setVendorId(rs.getInt(19));
                vb.setVendorName(rs.getString(20));
                pob.setVendorBean(vb);

                sb.setStoreTypeId(rs.getInt(28));
                sb.setStoreType(rs.getString(29));
                pob.setStoreTypeBean(sb);

                ub.setEmployeeName(rs.getString(36));
                pob.setUserBean(ub);

                list.add(pob);
            }
        } catch (Exception e) {
            System.out.println(e);
            return list;
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewAllApproval() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from purchase_order left join vendor on vendor.vendor_id=purchase_order.vendor_id left join store_type s on s.store_type_id=purchase_order.store_type_id left join users on users.user_id=purchase_order.user_id where APPROVAL_STATUS not in (1,2) and RECEIVE_STATUS!=1");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                PurchaseOrderBean pob = new PurchaseOrderBean();
                VendorBean vb = new VendorBean();
                StoreTypeBean sb = new StoreTypeBean();
                UserBean ub = new UserBean();

                pob.setOrderId(rs.getInt(1));
                pob.setVendorId(rs.getInt(2));
                pob.setStoreTypeId(rs.getInt(3));
                pob.setProductName(rs.getString(4));
                pob.setQty(rs.getInt(5));
                pob.setUnitPrice(rs.getInt(6));
                pob.setTax(rs.getInt(7));
                pob.settAmount(rs.getInt(8));
                pob.setApprovalStatus(rs.getInt(9));
                pob.setApprovedBy(rs.getInt(10));
                pob.setUserId(rs.getInt(11));
                pob.setPurchaseOrderDate(rs.getDate(12));
                pob.setPurchaseOrderNo(rs.getString(13));
                pob.setApprovedDate(rs.getDate(14));
                pob.setApprovalNo(rs.getInt(15));
                pob.setEntryDate(rs.getDate(16));
                pob.setDescription(rs.getString(17));
                pob.setReceiveStatus(rs.getInt(18));

                vb.setVendorId(rs.getInt(19));
                vb.setVendorName(rs.getString(20));
                pob.setVendorBean(vb);

                sb.setStoreTypeId(rs.getInt(28));
                sb.setStoreType(rs.getString(29));
                pob.setStoreTypeBean(sb);

                ub.setEmployeeName(rs.getString(36));
                pob.setUserBean(ub);

                list.add(pob);
            }
        } catch (Exception e) {
            System.out.println(e);
            return list;
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewAllApproved() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from purchase_order left join vendor on vendor.vendor_id=purchase_order.vendor_id left join store_type s on s.store_type_id=purchase_order.store_type_id left join users on users.user_id=purchase_order.user_id where APPROVAL_STATUS in (1) and RECEIVE_STATUS!=1");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                PurchaseOrderBean pob = new PurchaseOrderBean();
                VendorBean vb = new VendorBean();
                StoreTypeBean sb = new StoreTypeBean();
                UserBean ub = new UserBean();

                pob.setOrderId(rs.getInt(1));
                pob.setVendorId(rs.getInt(2));
                pob.setStoreTypeId(rs.getInt(3));
                pob.setProductName(rs.getString(4));
                pob.setQty(rs.getInt(5));
                pob.setUnitPrice(rs.getInt(6));
                pob.setTax(rs.getInt(7));
                pob.settAmount(rs.getInt(8));
                pob.setApprovalStatus(rs.getInt(9));
                pob.setApprovedBy(rs.getInt(10));
                pob.setUserId(rs.getInt(11));
                pob.setPurchaseOrderDate(rs.getDate(12));
                pob.setPurchaseOrderNo(rs.getString(13));
                pob.setApprovedDate(rs.getDate(14));
                pob.setApprovalNo(rs.getInt(15));
                pob.setEntryDate(rs.getDate(16));
                pob.setDescription(rs.getString(17));
                pob.setReceiveStatus(rs.getInt(18));

                vb.setVendorId(rs.getInt(19));
                vb.setVendorName(rs.getString(20));
                pob.setVendorBean(vb);

                sb.setStoreTypeId(rs.getInt(28));
                sb.setStoreType(rs.getString(29));
                pob.setStoreTypeBean(sb);

                ub.setEmployeeName(rs.getString(36));
                pob.setUserBean(ub);

                list.add(pob);
            }
        } catch (Exception e) {
            System.out.println(e);
            return list;
        } finally {
            con.close();
        }
        return list;
    }

    public static PurchaseOrderBean viewSpecific(String id) throws SQLException {
        PurchaseOrderBean pob = new PurchaseOrderBean();
        VendorBean vb = new VendorBean();
        StoreTypeBean sb = new StoreTypeBean();
        UserBean ub = new UserBean();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from purchase_order left join vendor on vendor.vendor_id=purchase_order.vendor_id left join store_type s on s.store_type_id=purchase_order.store_type_id left join users on users.user_id=purchase_order.user_id where POD_NUMBER=? and RECEIVE_STATUS!=1");
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {

                pob.setOrderId(rs.getInt(1));
                pob.setVendorId(rs.getInt(2));
                pob.setStoreTypeId(rs.getInt(3));
                pob.setProductName(rs.getString(4));
                pob.setQty(rs.getInt(5));
                pob.setUnitPrice(rs.getInt(6));
                pob.setTax(rs.getInt(7));
                pob.settAmount(rs.getInt(8));
                pob.setApprovalStatus(rs.getInt(9));
                pob.setApprovedBy(rs.getInt(10));
                pob.setUserId(rs.getInt(11));
                pob.setPurchaseOrderDate(rs.getDate(12));
                pob.setPurchaseOrderNo(rs.getString(13));
                pob.setApprovedDate(rs.getDate(14));
                pob.setApprovalNo(rs.getInt(15));
                pob.setEntryDate(rs.getDate(16));
                pob.setDescription(rs.getString(17));
                pob.setReceiveStatus(rs.getInt(18));

                vb.setVendorId(rs.getInt(19));
                vb.setVendorName(rs.getString(20));
                pob.setVendorBean(vb);

                sb.setStoreTypeId(rs.getInt(28));
                sb.setStoreType(rs.getString(29));
                pob.setStoreTypeBean(sb);

                ub.setEmployeeName(rs.getString(36));
                pob.setUserBean(ub);

            }
        } catch (Exception e) {
            System.out.println(e);
            return pob;
        } finally {
            con.close();
        }
        return pob;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from purchase_order where purchase_order_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {

        } finally {
            con.close();
        }
        return i;
    }

    public static int addReceiveOrder(ReceiveOrderBean pb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into receive_order values(RECEIVEORDERID.nextval,?,?,?,?,sysdate,sysdate)");
            pre.setInt(1, pb.getPurchaseOrderId());
            pre.setInt(2, pb.getUser_id());
            pre.setInt(3, pb.getReturnOutwards());
            pre.setFloat(4, pb.getTotalAmount());
            i = pre.executeUpdate();
            if (i != 0) {
                pre = con.prepareStatement("update PURCHASE_ORDER set RECEIVE_STATUS=1 where PURCHASE_ORDER_ID=?");
                pre.setInt(1, pb.getPurchaseOrderId());
                int j = pre.executeUpdate();
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int approveOrder(int id, int uid) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update purchase_order set approval_status=1, approved_by=?,approval_date=sysdate where purchase_order_id=?");
            pre.setInt(1, uid);
            pre.setInt(2, id);
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int disApproveOrder(int id, int uid) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update purchase_order set approval_status=2, approved_by=?,approval_date=sysdate where purchase_order_id=?");
            pre.setInt(1, uid);
            pre.setInt(2, id);
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int chkApprovalNo(int no) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from purchase_order where APPROVAL_NUMBER=?");
            pre.setInt(1, no);
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static Vector viewReceiveOrder() throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from RECEIVE_ORDER ro left join PURCHASE_ORDER po on ro.PURCHASE_ORDER_ID=po.PURCHASE_ORDER_ID left join users u on u.USER_ID=ro.USER_ID left join VENDOR v on po.VENDOR_ID=v.VENDOR_ID left join STORE_TYPE st on po.STORE_TYPE_ID=st.STORE_TYPE_ID");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                ReceiveOrderBean rob=new ReceiveOrderBean();
                PurchaseOrderBean pob=new PurchaseOrderBean();
                UserBean ub=new UserBean();
                VendorBean vb=new VendorBean();
                StoreTypeBean stb=new StoreTypeBean();
                rob.setReceiveOrderId(rs.getInt(1));
                rob.setPurchaseOrderId(rs.getInt(2));
                rob.setUser_id(rs.getInt(3));
                rob.setReturnOutwards(rs.getInt(4));
                rob.setTotalAmount(rs.getFloat(5));
                rob.setRod(rs.getDate(6));
                rob.setEntryDate(rs.getDate(7));
                
                pob.setOrderId(rs.getInt(8));
                pob.setVendorId(rs.getInt(9));
                pob.setStoreTypeId(rs.getInt(10));
                pob.setProductName(rs.getString(11));
                pob.setQty(rs.getInt(12));
                pob.setUnitPrice(rs.getInt(13));
                pob.setTax(rs.getInt(14));
                pob.settAmount(rs.getInt(15));
                pob.setApprovalStatus(rs.getInt(16));
                pob.setApprovedBy(rs.getInt(17));
                pob.setUserId(rs.getInt(18));
                pob.setPurchaseOrderDate(rs.getDate(19));
                pob.setPurchaseOrderNo(rs.getString(20));
                pob.setApprovedDate(rs.getDate(21));
                pob.setApprovalNo(rs.getInt(22));
                pob.setEntryDate(rs.getDate(23));
                pob.setDescription(rs.getString(24));
                pob.setReceiveStatus(rs.getInt(25));
                
                ub.setUserId(rs.getInt(26));
                ub.setDor(rs.getDate(27));
                ub.setPassword(rs.getString(28));
                ub.setUserType(rs.getString(29));
                ub.setEmail(rs.getString(30));
                ub.setEmployeeName(rs.getString(31));
                ub.setDob(rs.getDate(32));
                ub.setEntryDate(rs.getDate(33));
                ub.setUserStatus(rs.getInt(34));
                ub.setEntryEmail(rs.getString(35));
                
                vb.setVendorId(rs.getInt(36));
                vb.setVendorName(rs.getString(37));
                vb.setProductCategoryId(rs.getInt(38));
                vb.setAddress(rs.getString(39));
                vb.setTin(rs.getString(40));
                vb.setMobile(rs.getString(41));
                vb.setPhone(rs.getString(42));
                vb.setEntryDate(rs.getDate(43));
                vb.setEmail(rs.getString(44));
                pob.setVendorBean(vb);
                stb.setStoreTypeId(rs.getInt(45));
                stb.setStoreType(rs.getString(46));
                stb.setEntryDate(rs.getDate(47));
                pob.setStoreTypeBean(stb);
                rob.setPurchaseOrderBean(pob);
                rob.setUserBean(ub);
                list.add(rob);
            }
        }catch(Exception e){
            
        }finally{
            con.close();
        }return list;
    }
    public static Vector vendorWiseReceiveOrder(int value) throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from RECEIVE_ORDER ro left join PURCHASE_ORDER po on ro.PURCHASE_ORDER_ID=po.PURCHASE_ORDER_ID left join users u on u.USER_ID=ro.USER_ID left join VENDOR v on po.VENDOR_ID=v.VENDOR_ID left join STORE_TYPE st on po.STORE_TYPE_ID=st.STORE_TYPE_ID where v.vendor_id="+value+"");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                ReceiveOrderBean rob=new ReceiveOrderBean();
                PurchaseOrderBean pob=new PurchaseOrderBean();
                UserBean ub=new UserBean();
                VendorBean vb=new VendorBean();
                StoreTypeBean stb=new StoreTypeBean();
                rob.setReceiveOrderId(rs.getInt(1));
                rob.setPurchaseOrderId(rs.getInt(2));
                rob.setUser_id(rs.getInt(3));
                rob.setReturnOutwards(rs.getInt(4));
                rob.setTotalAmount(rs.getFloat(5));
                rob.setRod(rs.getDate(6));
                rob.setEntryDate(rs.getDate(7));
                
                pob.setOrderId(rs.getInt(8));
                pob.setVendorId(rs.getInt(9));
                pob.setStoreTypeId(rs.getInt(10));
                pob.setProductName(rs.getString(11));
                pob.setQty(rs.getInt(12));
                pob.setUnitPrice(rs.getInt(13));
                pob.setTax(rs.getInt(14));
                pob.settAmount(rs.getInt(15));
                pob.setApprovalStatus(rs.getInt(16));
                pob.setApprovedBy(rs.getInt(17));
                pob.setUserId(rs.getInt(18));
                pob.setPurchaseOrderDate(rs.getDate(19));
                pob.setPurchaseOrderNo(rs.getString(20));
                pob.setApprovedDate(rs.getDate(21));
                pob.setApprovalNo(rs.getInt(22));
                pob.setEntryDate(rs.getDate(23));
                pob.setDescription(rs.getString(24));
                pob.setReceiveStatus(rs.getInt(25));
                
                ub.setUserId(rs.getInt(26));
                ub.setDor(rs.getDate(27));
                ub.setPassword(rs.getString(28));
                ub.setUserType(rs.getString(29));
                ub.setEmail(rs.getString(30));
                ub.setEmployeeName(rs.getString(31));
                ub.setDob(rs.getDate(32));
                ub.setEntryDate(rs.getDate(33));
                ub.setUserStatus(rs.getInt(34));
                ub.setEntryEmail(rs.getString(35));
                
                vb.setVendorId(rs.getInt(36));
                vb.setVendorName(rs.getString(37));
                vb.setProductCategoryId(rs.getInt(38));
                vb.setAddress(rs.getString(39));
                vb.setTin(rs.getString(40));
                vb.setMobile(rs.getString(41));
                vb.setPhone(rs.getString(42));
                vb.setEntryDate(rs.getDate(43));
                vb.setEmail(rs.getString(44));
                pob.setVendorBean(vb);
                stb.setStoreTypeId(rs.getInt(45));
                stb.setStoreType(rs.getString(46));
                stb.setEntryDate(rs.getDate(47));
                pob.setStoreTypeBean(stb);
                rob.setPurchaseOrderBean(pob);
                rob.setUserBean(ub);
                list.add(rob);
            }
        }catch(Exception e){
            
        }finally{
            con.close();
        }return list;
    }
     public static Vector productWiseReceiveOrder(String value) throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from RECEIVE_ORDER ro left join PURCHASE_ORDER po on ro.PURCHASE_ORDER_ID=po.PURCHASE_ORDER_ID left join users u on u.USER_ID=ro.USER_ID left join VENDOR v on po.VENDOR_ID=v.VENDOR_ID left join STORE_TYPE st on po.STORE_TYPE_ID=st.STORE_TYPE_ID where upper(po.PRODUCT_NAME) like upper('"+value+"%')");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                ReceiveOrderBean rob=new ReceiveOrderBean();
                PurchaseOrderBean pob=new PurchaseOrderBean();
                UserBean ub=new UserBean();
                VendorBean vb=new VendorBean();
                StoreTypeBean stb=new StoreTypeBean();
                rob.setReceiveOrderId(rs.getInt(1));
                rob.setPurchaseOrderId(rs.getInt(2));
                rob.setUser_id(rs.getInt(3));
                rob.setReturnOutwards(rs.getInt(4));
                rob.setTotalAmount(rs.getFloat(5));
                rob.setRod(rs.getDate(6));
                rob.setEntryDate(rs.getDate(7));
                
                pob.setOrderId(rs.getInt(8));
                pob.setVendorId(rs.getInt(9));
                pob.setStoreTypeId(rs.getInt(10));
                pob.setProductName(rs.getString(11));
                pob.setQty(rs.getInt(12));
                pob.setUnitPrice(rs.getInt(13));
                pob.setTax(rs.getInt(14));
                pob.settAmount(rs.getInt(15));
                pob.setApprovalStatus(rs.getInt(16));
                pob.setApprovedBy(rs.getInt(17));
                pob.setUserId(rs.getInt(18));
                pob.setPurchaseOrderDate(rs.getDate(19));
                pob.setPurchaseOrderNo(rs.getString(20));
                pob.setApprovedDate(rs.getDate(21));
                pob.setApprovalNo(rs.getInt(22));
                pob.setEntryDate(rs.getDate(23));
                pob.setDescription(rs.getString(24));
                pob.setReceiveStatus(rs.getInt(25));
                
                ub.setUserId(rs.getInt(26));
                ub.setDor(rs.getDate(27));
                ub.setPassword(rs.getString(28));
                ub.setUserType(rs.getString(29));
                ub.setEmail(rs.getString(30));
                ub.setEmployeeName(rs.getString(31));
                ub.setDob(rs.getDate(32));
                ub.setEntryDate(rs.getDate(33));
                ub.setUserStatus(rs.getInt(34));
                ub.setEntryEmail(rs.getString(35));
                
                vb.setVendorId(rs.getInt(36));
                vb.setVendorName(rs.getString(37));
                vb.setProductCategoryId(rs.getInt(38));
                vb.setAddress(rs.getString(39));
                vb.setTin(rs.getString(40));
                vb.setMobile(rs.getString(41));
                vb.setPhone(rs.getString(42));
                vb.setEntryDate(rs.getDate(43));
                vb.setEmail(rs.getString(44));
                pob.setVendorBean(vb);
                stb.setStoreTypeId(rs.getInt(45));
                stb.setStoreType(rs.getString(46));
                stb.setEntryDate(rs.getDate(47));
                pob.setStoreTypeBean(stb);
                rob.setPurchaseOrderBean(pob);
                rob.setUserBean(ub);
                list.add(rob);
            }
        }catch(Exception e){
            
        }finally{
            con.close();
        }return list;
    }
     public static Vector StoreWiseReceiveOrder(int value) throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from RECEIVE_ORDER ro left join PURCHASE_ORDER po on ro.PURCHASE_ORDER_ID=po.PURCHASE_ORDER_ID left join users u on u.USER_ID=ro.USER_ID left join VENDOR v on po.VENDOR_ID=v.VENDOR_ID left join STORE_TYPE st on po.STORE_TYPE_ID=st.STORE_TYPE_ID where st.STORE_TYPE_ID="+value+"");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                ReceiveOrderBean rob=new ReceiveOrderBean();
                PurchaseOrderBean pob=new PurchaseOrderBean();
                UserBean ub=new UserBean();
                VendorBean vb=new VendorBean();
                StoreTypeBean stb=new StoreTypeBean();
                rob.setReceiveOrderId(rs.getInt(1));
                rob.setPurchaseOrderId(rs.getInt(2));
                rob.setUser_id(rs.getInt(3));
                rob.setReturnOutwards(rs.getInt(4));
                rob.setTotalAmount(rs.getFloat(5));
                rob.setRod(rs.getDate(6));
                rob.setEntryDate(rs.getDate(7));
                
                pob.setOrderId(rs.getInt(8));
                pob.setVendorId(rs.getInt(9));
                pob.setStoreTypeId(rs.getInt(10));
                pob.setProductName(rs.getString(11));
                pob.setQty(rs.getInt(12));
                pob.setUnitPrice(rs.getInt(13));
                pob.setTax(rs.getInt(14));
                pob.settAmount(rs.getInt(15));
                pob.setApprovalStatus(rs.getInt(16));
                pob.setApprovedBy(rs.getInt(17));
                pob.setUserId(rs.getInt(18));
                pob.setPurchaseOrderDate(rs.getDate(19));
                pob.setPurchaseOrderNo(rs.getString(20));
                pob.setApprovedDate(rs.getDate(21));
                pob.setApprovalNo(rs.getInt(22));
                pob.setEntryDate(rs.getDate(23));
                pob.setDescription(rs.getString(24));
                pob.setReceiveStatus(rs.getInt(25));
                
                ub.setUserId(rs.getInt(26));
                ub.setDor(rs.getDate(27));
                ub.setPassword(rs.getString(28));
                ub.setUserType(rs.getString(29));
                ub.setEmail(rs.getString(30));
                ub.setEmployeeName(rs.getString(31));
                ub.setDob(rs.getDate(32));
                ub.setEntryDate(rs.getDate(33));
                ub.setUserStatus(rs.getInt(34));
                ub.setEntryEmail(rs.getString(35));
                
                vb.setVendorId(rs.getInt(36));
                vb.setVendorName(rs.getString(37));
                vb.setProductCategoryId(rs.getInt(38));
                vb.setAddress(rs.getString(39));
                vb.setTin(rs.getString(40));
                vb.setMobile(rs.getString(41));
                vb.setPhone(rs.getString(42));
                vb.setEntryDate(rs.getDate(43));
                vb.setEmail(rs.getString(44));
                pob.setVendorBean(vb);
                stb.setStoreTypeId(rs.getInt(45));
                stb.setStoreType(rs.getString(46));
                stb.setEntryDate(rs.getDate(47));
                pob.setStoreTypeBean(stb);
                rob.setPurchaseOrderBean(pob);
                rob.setUserBean(ub);
                list.add(rob);
            }
        }catch(Exception e){
            
        }finally{
            con.close();
        }return list;
    }
}
